package com.tentact.coolmeetingtest.dao;

import com.tentact.coolmeetingtest.support.RetMsg;
import com.tentact.coolmeetingtest.support.VoAutoBuilder;
import com.tentact.coolmeetingtest.util.JDBCUtil;
import com.tentact.coolmeetingtest.util.OtherUtil;
import com.tentact.coolmeetingtest.vo.Emp;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * @author 星环
 * @date 2023/1/10 15:24:45
 * @description
 */
public class EmpDao {
    public Emp getByUsernameAndPassword(String username,String pwd){
        List<Emp> excute = (List<Emp>) JDBCUtil.excute("select * from emp where username = ? and pwd = ?", Emp.class, username, pwd);
        return OtherUtil.getFirstDefaultNull(excute);
    }

    public List<Emp> getbyUsername(String username) {
        return (List<Emp>) JDBCUtil.excute("select * from emp where username = ?",Emp.class,username);
    }

    public int addEmp(Emp emp) {
        return (Integer)JDBCUtil.excute("insert into emp(empno,emp_name,username,pwd,tel,email,deptno,state) values(null,?,?,?, ?,?,?,0)",
                Integer.class, emp.getEmpName(), emp.getUsername(), emp.getPwd(), emp.getTel(), emp.getEmail(), emp.getDeptno());

    }

    public List<Emp> getAll() {
        return (List<Emp>) JDBCUtil.excute("select * from emp",Emp.class);
    }

    public List<Emp> getEmpByState(int state) {
        return (List<Emp>) JDBCUtil.excute("select * from emp where state = ?",Emp.class,state);
    }

    public int updateStateByEmpno(Integer empno, int state) {
        return (Integer) JDBCUtil.excute("update emp set state = ? where empno = ?", Integer.class, state, empno);
    }

    public int deleteEmpByEmpno(Integer empno) {
        return (Integer) JDBCUtil.excute("delete from emp where empno = ?", Integer.class, empno);

    }

    public List<Emp> getEmpByState0(int state){
        PreparedStatement preparedStatement = JDBCUtil.getPreparedStatement("select empno,emp_name,email,d.dept_name from emp e inner join dept d on e.deptno = d.deptno where e.state = ?", state);
        ResultSet resultSet = null;
        try {
            resultSet = preparedStatement.executeQuery();
            return new VoAutoBuilder<Emp>().buildAll(resultSet, Emp.class, "empno", "emp_name", "email", "dept_name");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtil.close(preparedStatement,resultSet);
        }
    }

    private String getSearchEmpSql(Emp emp){
        String sql = "select empno,emp_name,username,tel,email from emp ";
        String query = "";
        if(emp.getEmpName() != null){
            query += " and emp_name = '" + emp.getEmpName()+"' ";
        }
        if(emp.getUsername() != null){
            query += " and username = '" + emp.getUsername()+"' ";
        }
        if(emp.getState() != null){
            query += " and state = " + emp.getState().getState();
        }
        if(query.length() != 0){
            query = query.substring(4);
            sql += " where " + query;
        }
        return sql;
    }

    private String getSearchEmpSqlLimited(Emp emp,int start,int i_per_page){
        String sql = getSearchEmpSql(emp);
        sql += " limit " + start + "," + i_per_page;
        return sql;
    }

    public List<Emp> serchEmp(Emp emp) {

        PreparedStatement ps = JDBCUtil.getPreparedStatement(getSearchEmpSql(emp));
        ResultSet r = null;
        try {
            r = ps.executeQuery();
            return new VoAutoBuilder<Emp>().buildAll(r,Emp.class,"emp_name","username","tel","email");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public List<Emp> serchEmpLimited(Emp emp, int start, int i_per_page) {
        String sql = getSearchEmpSqlLimited(emp, start, i_per_page);
        System.out.println(sql);
        PreparedStatement ps = JDBCUtil.getPreparedStatement(sql);
        ResultSet r = null;
        try {
            r = ps.executeQuery();
            return new VoAutoBuilder<Emp>().buildAll(r,Emp.class,"emp_name","username","tel","email","empno");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtil.close(ps,r);
        }
    }

    public int getEmpTotalCount(Emp emp){
        String sql = getSearchEmpSql(emp);
        int fIndex = sql.indexOf("from");
        sql = "select count(1) as count" + " " + sql.substring(fIndex);
        PreparedStatement ps = JDBCUtil.getPreparedStatement(sql);
        ResultSet resultSet = null;
        try {
            resultSet = ps.executeQuery();
            if (resultSet.next()) {
                return resultSet.getInt(1);
            }
            return 0;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    public int getEmpnoByUsername(String username) {
        //List<Integer> noList = (List<Integer>)JDBCUtil.excute("select empno from emp where username = ?", Integer.class, username);
        String sql = "select empno from emp where username = ?";
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        ps = JDBCUtil.getPreparedStatement(sql,username);
        try {
            resultSet = ps.executeQuery();
            if (resultSet.next()) {
                return resultSet.getInt(1);
            }
            return -1;

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.close(ps,resultSet);
        }
        return -1;
    }

    public boolean checkExistByEmpno(int empno) {
        String sql = "select count(1) from emp where empno = ?";
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            resultSet = JDBCUtil.getPreparedStatement(sql, empno).executeQuery();
            if(resultSet.next()){
                return resultSet.getInt(1) > 0;
            }
            return false;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtil.close(ps,resultSet);
        }
    }
}
